/**********************************************************************************************************************
 * SplendidCRM is a Customer Relationship Management program created by SplendidCRM Software, Inc. 
 * Copyright (C) 2005-2011 SplendidCRM Software, Inc. All rights reserved.
 * 
 * This program is free software: you can redistribute it and/or modify it under the terms of the 
 * GNU Affero General Public License as published by the Free Software Foundation, either version 3 
 * of the License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; 
 * without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. 
 * See the GNU Affero General Public License for more details.
 * 
 * You should have received a copy of the GNU Affero General Public License along with this program. 
 * If not, see <http://www.gnu.org/licenses/>. 
 * 
 * You can contact SplendidCRM Software, Inc. at email address support@splendidcrm.com. 
 * 
 * In accordance with Section 7(b) of the GNU Affero General Public License version 3, 
 * the Appropriate Legal Notices must display the following words on all interactive user interfaces: 
 * "Copyright (C) 2005-2011 SplendidCRM Software, Inc. All rights reserved."
 *********************************************************************************************************************/
using System;
using System.Xml;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Collections;
using System.Diagnostics;

namespace SplendidCRM
{
	/// <summary>
	/// Summary description for Utils.
	/// </summary>
	public class Utils
	{
		public static void SetPageTitle(Page page, string sTitle)
		{
			try
			{
				Literal litPageTitle = page.FindControl("litPageTitle") as Literal;
				if ( litPageTitle != null )
					litPageTitle.Text = sTitle;
			}
			catch(Exception ex)
			{
				SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
			}
		}

		// 10/20/2010 Paul.  We are still having a problem with the Enter Key hijacking the Auto-Complete logic. The most practical solution is to block the Enter Key. 
		public static string PreventEnterKeyPress(string sTextID)
		{
			// 05/09/2008 Paul.  There is the rare possibility that the IDs are NULL.  Such as on the login page. 
			if ( !Sql.IsEmptyString(sTextID) )
			{
				// 03/28/2007 Paul.  Fix to support Firefox, which passes the event object in the first parameter. 
				StringBuilder sb = new StringBuilder();
				sb.AppendLine("<script type=\"text/javascript\">");
				// 06/21/2009 Paul.  We are having issues with the controls not being found.  Report the error instead of throwing an exception. 
				sb.AppendLine("if ( document.getElementById('" + sTextID + "') != null )");
				sb.AppendLine("{");
				sb.AppendLine(" document.getElementById('" + sTextID + "').onkeypress = function(e)");
				sb.AppendLine(" {");
				sb.AppendLine("  if ( e != null )");
				sb.AppendLine("  {");
				sb.AppendLine("   if ( e.which == 13 )");
				sb.AppendLine("   {");
				sb.AppendLine("    return false;");
				sb.AppendLine("   }");
				sb.AppendLine("  }");
				sb.AppendLine("  else if ( event != null )");
				sb.AppendLine("  {");
				sb.AppendLine("   if ( event.keyCode == 13 )");
				sb.AppendLine("   {");
				sb.AppendLine("    event.returnValue = false;");
				sb.AppendLine("    event.cancel = true;");
				sb.AppendLine("   }");
				sb.AppendLine("  }");
				sb.AppendLine(" }");
				sb.AppendLine("}");
				sb.AppendLine("</script>");
				return sb.ToString();
			}
			return String.Empty;
		}

		public static string RegisterEnterKeyPress(string sTextID, string sButtonID)
		{
			// 05/09/2008 Paul.  There is the rare possibility that the IDs are NULL.  Such as on the login page. 
			if ( !Sql.IsEmptyString(sTextID) && !Sql.IsEmptyString(sButtonID) )
			{
				// 03/28/2007 Paul.  Fix to support Firefox, which passes the event object in the first parameter. 
				StringBuilder sb = new StringBuilder();
				sb.AppendLine("<script type=\"text/javascript\">");
				// 06/21/2009 Paul.  We are having issues with the controls not being found.  Report the error instead of throwing an exception. 
				sb.AppendLine("if ( document.getElementById('" + sTextID + "') != null && document.getElementById('" + sButtonID + "') != null )");
				sb.AppendLine("{");
				sb.AppendLine(" document.getElementById('" + sTextID + "').onkeypress = function(e)");
				sb.AppendLine(" {");
				sb.AppendLine("  if ( e != null )");
				sb.AppendLine("  {");
				sb.AppendLine("   if ( e.which == 13 )");
				sb.AppendLine("   {");
				sb.AppendLine("    document.getElementById('" + sButtonID + "').click();");
				sb.AppendLine("    return false;");
				sb.AppendLine("   }");
				sb.AppendLine("  }");
				sb.AppendLine("  else if ( event != null )");
				sb.AppendLine("  {");
				sb.AppendLine("   if ( event.keyCode == 13 )");
				sb.AppendLine("   {");
				sb.AppendLine("    event.returnValue = false;");
				sb.AppendLine("    event.cancel = true;");
				sb.AppendLine("    document.getElementById('" + sButtonID + "').click();");
				sb.AppendLine("   }");
				sb.AppendLine("  }");
				sb.AppendLine(" }");
				sb.AppendLine("}");
// 01/13/2010 Paul.  Don't need these alerts any more. 
#if false
				sb.AppendLine("else");
				sb.AppendLine("{");
				sb.AppendLine(" alert('Could not find " + sTextID + " for EnterKey processing');");
				sb.AppendLine(" if ( document.getElementById('" + sButtonID + "') == null )");
				sb.AppendLine("  alert('Could not find " + sButtonID + " for EnterKey processing');");
				sb.AppendLine("}");
#endif

				sb.AppendLine("</script>");
				return sb.ToString();
			}
			return String.Empty;
		}

		public static string RegisterSetFocus(string sTextID)
		{
			StringBuilder sb = new StringBuilder();
			sb.AppendLine("<script type=\"text/javascript\">");
			// 09/07/2009 Paul.  Prevent a runtime error by checking if the element exists before setting the focus. 
			sb.AppendLine("if ( document.getElementById('" + sTextID + "') == null )");
			sb.AppendLine("	document.getElementById('" + sTextID + "').focus();");
			sb.AppendLine("</script>");
			return sb.ToString();
		}
	
		public static WebControl CreateArrowControl(bool bAscending)
		{
			Label lblArrow = new Label();
			lblArrow.Font.Name = "Webdings";
			if ( bAscending )
				lblArrow.Text = "5";
			else
				lblArrow.Text = "6";
			return lblArrow;
		}

		public static string ValidateIDs(string[] arrID, bool bQuoted)
		{
			if ( arrID.Length == 0 )
				return String.Empty;
			if ( arrID.Length > 200 )
			{
				L10N L10n = HttpContext.Current.Items["L10n"] as L10N;
				throw(new Exception(L10n.Term(".LBL_TOO_MANY_RECORDS")));
			}
			
			foreach(string sID in arrID)
			{
				Guid gID = Sql.ToGuid(sID);
				if ( Sql.IsEmptyGuid(gID) )
				{
					// 05/02/2006 Paul.  Provide a more descriptive error message by including the ID. 
					throw(new Exception("Invalid ID: " + sID));
				}
			}
			string sIDs = String.Empty;
			if ( bQuoted )
				sIDs = "'" + String.Join("','", arrID) + "'";
			else
				sIDs = String.Join(",", arrID);
			return sIDs;
		}

		public static string ValidateIDs(string[] arrID)
		{
			return ValidateIDs(arrID, false);
		}

		public static string FilterByACL(string sMODULE_NAME, string sACCESS_TYPE, string[] arrID, string sTABLE_NAME)
		{
			StringBuilder sb = new StringBuilder();
			int nACLACCESS = Security.GetUserAccess(sMODULE_NAME, sACCESS_TYPE);
			if ( nACLACCESS >= 0 && arrID.Length > 0 )
			{
				if ( nACLACCESS == ACL_ACCESS.OWNER )
				{
					DbProviderFactory dbf = DbProviderFactories.GetFactory();
					using ( IDbConnection con = dbf.CreateConnection() )
					{
						// 09/26/2006 Paul.  The connection needed to be opened. 
						con.Open();
						string sSQL;
						sSQL = "select ID              " + ControlChars.CrLf
						     + "  from vw" + sTABLE_NAME + ControlChars.CrLf
						     + " where 1 = 1           " + ControlChars.CrLf;
						using ( IDbCommand cmd = con.CreateCommand() )
						{
							cmd.CommandText = sSQL;
							Sql.AppendGuids(cmd, arrID, "ID");
							Sql.AppendParameter(cmd, Security.USER_ID, "ASSIGNED_USER_ID", false);
							// 10/16/2006 Paul.  Fix execute to allow more than one row. 
							using ( IDataReader rdr = cmd.ExecuteReader() )
							{
								while ( rdr.Read() )
								{
									if ( sb.Length > 0 )
										sb.Append(",");
									sb.Append(Sql.ToString(rdr["ID"]));
								}
							}
						}
					}
					if ( sb.Length == 0 )
					{
						L10N L10n = HttpContext.Current.Items["L10n"] as L10N;
						throw(new Exception(L10n.Term("ACL.LBL_INSUFFICIENT_ACCESS")));
					}
				}
				else
				{
					return String.Join(",", arrID);
				}
			}
			return sb.ToString();
		}

		public static string BuildMassIDs(Stack stk, int nCapacity)
		{
			if ( stk.Count == 0 )
				return String.Empty;
			
			StringBuilder sb = new StringBuilder();
			for ( int i = 0; i < nCapacity && stk.Count > 0; i++ )
			{
				string sID = Sql.ToString(stk.Pop());
				if ( sb.Length > 0 )
					sb.Append(",");
				sb.Append(sID);
			}
			return sb.ToString();;
		}

		public static string BuildMassIDs(Stack stkID)
		{
			return BuildMassIDs(stkID, 200);
		}

		public static Stack FilterByACL_Stack(string sMODULE_NAME, string sACCESS_TYPE, string[] arrID, string sTABLE_NAME)
		{
			Stack stk = new Stack();
			StringBuilder sb = new StringBuilder();
			int nACLACCESS = Security.GetUserAccess(sMODULE_NAME, sACCESS_TYPE);
			if ( nACLACCESS >= 0 && arrID.Length > 0 )
			{
				if ( nACLACCESS == ACL_ACCESS.OWNER )
				{
					DbProviderFactory dbf = DbProviderFactories.GetFactory();
					using ( IDbConnection con = dbf.CreateConnection() )
					{
						// 09/26/2006 Paul.  The connection needed to be opened. 
						con.Open();
						string sSQL;
						sSQL = "select ID              " + ControlChars.CrLf
						     + "  from vw" + sTABLE_NAME + ControlChars.CrLf
						     + " where 1 = 1           " + ControlChars.CrLf;
						using ( IDbCommand cmd = con.CreateCommand() )
						{
							cmd.CommandText = sSQL;
							Sql.AppendGuids(cmd, arrID, "ID");
							Sql.AppendParameter(cmd, Security.USER_ID, "ASSIGNED_USER_ID", false);
							using ( IDataReader rdr = cmd.ExecuteReader() )
							{
								while ( rdr.Read() )
								{
									stk.Push(Sql.ToString(rdr["ID"]));
								}
							}
						}
					}
					if ( stk.Count == 0 )
					{
						L10N L10n = HttpContext.Current.Items["L10n"] as L10N;
						throw(new Exception(L10n.Term("ACL.LBL_INSUFFICIENT_ACCESS")));
					}
				}
				else
				{
					foreach ( string sID in arrID )
					{
						if ( sID.Length > 0 )
							stk.Push(sID);
					}
				}
			}
			return stk;
		}

		public static void UpdateTracker(Page pParent, string sModule, Guid gID, string sName)
		{
			// 08/21/2005 Paul.  This function is also called after a user clicks Duplicate.
			// In this scenerio, the gID will be NULL, so don't do anything. 
			if ( !Sql.IsEmptyGuid(gID) )
			{
				SqlProcs.spTRACKER_Update(Security.USER_ID, sModule, gID, sName);
				if ( pParent != null )
				{
					// 02/08/2007 Paul.  The control is in the master page. 
					ContentPlaceHolder plcLastViewed = pParent.Master.FindControl("cntLastViewed") as ContentPlaceHolder;
					if ( plcLastViewed != null )
					{
						_controls.LastViewed ctlLastViewed = plcLastViewed.FindControl("ctlLastViewed") as _controls.LastViewed;
						if ( ctlLastViewed != null )
						{
							ctlLastViewed.Refresh();
							return;
						}
					}
					Themes.Six.TabMenu ctlTabMenu = pParent.Master.FindControl("ctlTabMenu") as Themes.Six.TabMenu;
					if ( ctlTabMenu != null )
					{
						ctlTabMenu.Refresh();
						return;
					}
				}
			}
		}

		public static void AdminShortcuts(Page pParent, bool bAdminShortcuts)
		{
			// 02/08/2007 Paul.  The control is in the master page. 
			ContentPlaceHolder plcShortcuts = pParent.Master.FindControl("cntSidebar") as ContentPlaceHolder;
			if ( plcShortcuts != null )
			{
				_controls.Shortcuts ctlShortcuts = plcShortcuts.FindControl("ctlShortcuts") as _controls.Shortcuts;
				if ( ctlShortcuts != null )
				{
					ctlShortcuts.AdminShortcuts = bAdminShortcuts;
				}
			}
		}

		// 08/19/2010 Paul.  Use ListControl to be more generic. 
		public static void SetValue(ListControl lst, string sValue)
		{
			for ( int i=0 ; i < lst.Items.Count; i++ )
			{
				if ( String.Compare(lst.Items[i].Value, sValue, true) == 0 )
				{
					// 08/19/2010 Paul.  We are doing a case-insignificant search, but then trying to set the case-significant value. 
					// Instead, just use the value of the index. 
					//lst.SelectedValue = lst.Items[i].Value;
					lst.SelectedIndex = i;
					break;
				}
			}
		}

		// 08/19/2010 Paul.  Check the list before assigning the value. 
		public static bool SetSelectedValue(ListControl lst, string sValue)
		{
			bool bFound = false;
			ListItem itm = lst.Items.FindByValue(sValue);
			if ( itm != null )
			{
				lst.SelectedValue = sValue;
				bFound = true;
			}
			return bFound;
		}

		public static string ExpandException(Exception ex)
		{
			StringBuilder sb = new StringBuilder();
			do
			{
				sb.Append(ex.Message);
				// 08/13/2007 Paul.  Only add the line break if there is more data. 
				if ( ex.InnerException != null )
					sb.Append("<br />\r\n");
				ex = ex.InnerException;
			}
			while ( ex != null );
			return sb.ToString();
		}

		public static string GetUserEmail(Guid gID)
		{
			string sEmail = String.Empty;
			if ( !Sql.IsEmptyGuid(gID) )
			{
				DbProviderFactory dbf = DbProviderFactories.GetFactory();
				using ( IDbConnection con = dbf.CreateConnection() )
				{
					con.Open();
					string sSQL;
					sSQL = "select EMAIL1  " + ControlChars.CrLf
					     + "     , EMAIL2  " + ControlChars.CrLf
					     + "  from vwUSERS " + ControlChars.CrLf
					     + " where ID = @ID" + ControlChars.CrLf;
					using ( IDbCommand cmd = con.CreateCommand() )
					{
						cmd.CommandText = sSQL;
						Sql.AddParameter(cmd, "@ID", gID);
						using ( IDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow) )
						{
							while ( rdr.Read() )
							{
								sEmail = Sql.ToString(rdr["EMAIL1"]);
								if ( Sql.IsEmptyString(sEmail) )
									sEmail = Sql.ToString(rdr["EMAIL2"]);
							}
						}
					}
				}
			}
			return sEmail;
		}

		public static System.Collections.Specialized.NameValueCollection AppSettings
		{
			get
			{
				#pragma warning disable 618
				return System.Configuration.ConfigurationSettings.AppSettings;
				#pragma warning restore 618
			}
		}

		// 11/06/2009 Paul.  We need a common way to detect the offline client. 
		public static bool IsOfflineClient
		{
			get
			{
				string sOfflineClient = Utils.AppSettings["OfflineClient"];
				return Sql.ToBoolean(sOfflineClient);
			}
		}

		public static bool IsMobileDevice
		{
			get
			{
				// 11/24/2010 Paul.  .NET 4 has broken the compatibility of the browser file system. 
				// We are going to minimize our reliance on browser files in order to reduce deployment issues. 
				return Sql.ToBoolean(HttpContext.Current.Session["IsMobileDevice"]);
			}
		}

		public static bool SupportsPopups
		{
			get
			{
				// 11/24/2010 Paul.  .NET 4 has broken the compatibility of the browser file system. 
				// We are going to minimize our reliance on browser files in order to reduce deployment issues. 
				return Sql.ToBoolean(HttpContext.Current.Session["SupportsPopups"]);
			}
		}

		public static bool AllowAutoComplete
		{
			get
			{
				// 11/24/2010 Paul.  .NET 4 has broken the compatibility of the browser file system. 
				// We are going to minimize our reliance on browser files in order to reduce deployment issues. 
				return Sql.ToBoolean(HttpContext.Current.Session["AllowAutoComplete"]);
			}
		}

		public static string TableColumnName(L10N L10n, string sModule, string sDISPLAY_NAME)
		{
			// 07/04/2006 Paul.  Some columns have global terms. 
			// 06/05/2007 Paul.  Add Team global term. 
			// 07/23/2008 Paul.  Add Audit terms. 
			// 10/04/2008 Paul.  CREATED_BY_ID is used in most module views. 
			// 12/03/2008 Paul.  AUDIT_TABLE is also a common field. 
			// 08/24/2009 Paul.  Add support for dynamic teams. 
			// 09/16/2009 Paul.  DATE_MODIFIED_UTC is a new common field used to sync. 
			// 09/01/2010 Paul.  Add CREATED_BY_NAME, MODIFIED_BY_NAME and ASSIGNED_TO_NAME as global names. 
			// 10/29/2010 Paul.  Add TEAM_SET_LIST. 
			// 12/07/2010 Paul.  Add TEAM_SET_ID. 
			if (  sDISPLAY_NAME == "ID"              
			   || sDISPLAY_NAME == "DELETED"         
			   || sDISPLAY_NAME == "CREATED_BY"      
			   || sDISPLAY_NAME == "CREATED_BY_ID"   
			   || sDISPLAY_NAME == "CREATED_BY_NAME" 
			   || sDISPLAY_NAME == "DATE_ENTERED"    
			   || sDISPLAY_NAME == "MODIFIED_USER_ID"
			   || sDISPLAY_NAME == "DATE_MODIFIED"   
			   || sDISPLAY_NAME == "DATE_MODIFIED_UTC"
			   || sDISPLAY_NAME == "MODIFIED_BY"     
			   || sDISPLAY_NAME == "MODIFIED_BY_ID"  
			   || sDISPLAY_NAME == "MODIFIED_BY_NAME"
			   || sDISPLAY_NAME == "ASSIGNED_USER_ID"
			   || sDISPLAY_NAME == "ASSIGNED_TO"     
			   || sDISPLAY_NAME == "ASSIGNED_TO_NAME"
			   || sDISPLAY_NAME == "TEAM_ID"         
			   || sDISPLAY_NAME == "TEAM_NAME"       
			   || sDISPLAY_NAME == "TEAM_SET_ID"     
			   || sDISPLAY_NAME == "TEAM_SET_NAME"   
			   || sDISPLAY_NAME == "TEAM_SET_LIST"   
			   || sDISPLAY_NAME == "ID_C"            
			   || sDISPLAY_NAME == "AUDIT_ID"        
			   || sDISPLAY_NAME == "AUDIT_ACTION"    
			   || sDISPLAY_NAME == "AUDIT_DATE"      
			   || sDISPLAY_NAME == "AUDIT_COLUMNS"   
			   || sDISPLAY_NAME == "AUDIT_TABLE"     
			   || sDISPLAY_NAME == "AUDIT_TOKEN"     
				)
			{
				sDISPLAY_NAME = L10n.Term(".LBL_" + sDISPLAY_NAME).Replace(":", "");
			}
			else
			{
				// 07/04/2006 Paul.  Column names are aliased so that we don't have to redefine terms. 
				sDISPLAY_NAME = L10n.AliasedTerm(sModule + ".LBL_" + sDISPLAY_NAME).Replace(":", "");
			}
			return sDISPLAY_NAME;
		}

		public static string MassEmailerSiteURL(HttpApplicationState Application)
		{
			string sSiteURL = Sql.ToString(Application["CONFIG.site_url"]);
			if ( Sql.ToString(Application["CONFIG.massemailer_tracking_entities_location_type"]) == "2" && !Sql.IsEmptyString(Application["CONFIG.massemailer_tracking_entities_location"]) )
				sSiteURL = Sql.ToString(Application["CONFIG.massemailer_tracking_entities_location"]);
			if ( Sql.IsEmptyString(sSiteURL) )
			{
				// 12/15/2007 Paul.  Use the environment as it is always available. 
				// The Request object is not always available, such as when inside a timer event. 
				// 12/22/2007 Paul.  We are now storing the server name in an application variable. 
				string sServerName      = Sql.ToString(Application["ServerName"     ]);
				string sApplicationPath = Sql.ToString(Application["ApplicationPath"]);
				sSiteURL = sServerName + sApplicationPath;
			}
			if ( !sSiteURL.StartsWith("http") )
				sSiteURL = "http://" + sSiteURL;
			if ( !sSiteURL.EndsWith("/") )
				sSiteURL += "/";
			return sSiteURL;
		}

		public static void RefreshAllViews()
		{
			// 05/08/2007 Paul.  Keep the original procedure call so that we will get a compiler error if something changes. 
			bool bIncreaseTimeout = true;
			if ( !bIncreaseTimeout )
			{
				SqlProcs.spSqlRefreshAllViews();
			}
			else
			{
				DbProviderFactory dbf = DbProviderFactories.GetFactory();
				using ( IDbConnection con = dbf.CreateConnection() )
				{
					con.Open();
					// 10/07/2009 Paul.  We need to create our own global transaction ID to support auditing and workflow on SQL Azure, PostgreSQL, Oracle, DB2 and MySQL. 
					using ( IDbTransaction trn = Sql.BeginTransaction(con) )
					{
						try
						{
							using ( IDbCommand cmd = con.CreateCommand() )
							{
								cmd.Transaction = trn;
								cmd.CommandType = CommandType.StoredProcedure;
								cmd.CommandText = "spSqlRefreshAllViews";
								// 05/08/2007 Paul.  Allow this to run until it completes. 
								cmd.CommandTimeout = 0;
								cmd.ExecuteNonQuery();
							}
							trn.Commit();
						}
						catch(Exception ex)
						{
							trn.Rollback();
							throw(new Exception(ex.Message, ex.InnerException));
						}
					}
				}
			}
		}

		// 12/12/2009 Paul.  We are going to call inside a thread as it can take a minute to run. 
		public static void UpdateSemanticModel(object o)
		{
			HttpContext Context = o as HttpContext;
			DbProviderFactory dbf = DbProviderFactories.GetFactory(Context.Application);
			using ( IDbConnection con = dbf.CreateConnection() )
			{
				con.Open();
				using ( IDbTransaction trn = Sql.BeginTransaction(con) )
				{
					try
					{
						using ( IDbCommand cmd = con.CreateCommand() )
						{
							cmd.Transaction = trn;
							cmd.CommandType = CommandType.StoredProcedure;
							cmd.CommandText = "spSEMANTIC_MODEL_Rebuild";
							cmd.CommandTimeout = 0;
							cmd.ExecuteNonQuery();
						}
						trn.Commit();
					}
					catch(Exception ex)
					{
						trn.Rollback();
						SplendidError.SystemMessage(Context, "Error", new StackTrace(true).GetFrame(0), ex);
					}
				}
			}
			// 12/12/2009 Paul.  The semantic model is user-specific. 
			SplendidCache.ClearSet("SEMANTIC_MODEL.");
		}

		public static void BuildAllAuditTables()
		{
			// 12/02/2009 Paul.  Keep the original procedure call so that we will get a compiler error if something changes. 
			bool bIncreaseTimeout = true;
			if ( !bIncreaseTimeout )
			{
				// 12/10/2009 Paul.  The Offline Client does not support auditing. 
				//SqlProcs.spSqlBuildAllAuditTables();
			}
			else
			{
				DbProviderFactory dbf = DbProviderFactories.GetFactory();
				using ( IDbConnection con = dbf.CreateConnection() )
				{
					con.Open();
					// 10/07/2009 Paul.  We need to create our own global transaction ID to support auditing and workflow on SQL Azure, PostgreSQL, Oracle, DB2 and MySQL. 
					using ( IDbTransaction trn = Sql.BeginTransaction(con) )
					{
						try
						{
							using ( IDbCommand cmd = con.CreateCommand() )
							{
								cmd.Transaction = trn;
								cmd.CommandType = CommandType.StoredProcedure;
								cmd.CommandText = "spSqlBuildAllAuditTables";
								// 12/02/2009 Paul.  Allow this to run until it completes. 
								cmd.CommandTimeout = 0;
								cmd.ExecuteNonQuery();
							}
							trn.Commit();
						}
						catch(Exception ex)
						{
							trn.Rollback();
							throw(new Exception(ex.Message, ex.InnerException));
						}
					}
				}
			}
		}

		public static DataTable CheckVersion(HttpApplicationState Application)
		{
			XmlDocument xml = new XmlDocument();
			// 12/18/2008 Paul.  Check the version of the appropriate service level. 
			string sVersionXmlURL = String.Empty;
			string sServiceLevel = Sql.ToString(Application["CONFIG.service_level"]);
			if ( String.Compare(sServiceLevel, "Basic", true) == 0 || String.Compare(sServiceLevel, "Community", true) == 0 )
				sVersionXmlURL = "http://community.splendidcrm.com/Administration/Versions.xml";
			else if ( String.Compare(sServiceLevel, "Enterprise", true) == 0 )
				sVersionXmlURL = "http://enterprise.splendidcrm.com/Administration/Versions.xml";
			else // if ( String.Compare(sServiceLevel, "Professional", true) == 0 )
				sVersionXmlURL = "http://professional.splendidcrm.com/Administration/Versions.xml";

			xml.Load(sVersionXmlURL + (Sql.ToBoolean(Application["CONFIG.send_usage_info"]) ? Utils.UsageInfo(Application) : String.Empty));

			Version vSplendidVersion = new Version(Sql.ToString(Application["SplendidVersion"]));
			DataTable dt = XmlUtil.CreateDataTable(xml.DocumentElement, "Version", new string[] {"Build", "Date", "Description", "URL", "New"});
			foreach ( DataRow row in dt.Rows )
			{
				Version vBuild = new Version(Sql.ToString(row["Build"]));
				if ( vSplendidVersion < vBuild )
					row["New"] = "1";
			}
			return dt;
		}

		public static string UsageInfo(HttpApplicationState Application)
		{
			StringBuilder sb = new StringBuilder();
			//sb.Append("&Machine="  + HttpUtility.UrlEncode(System.Environment.MachineName                   ));
			//sb.Append("&Procs="    + HttpUtility.UrlEncode(System.Environment.ProcessorCount.ToString()     ));
			//sb.Append("&IP="       + HttpUtility.UrlEncode(Sql.ToString(Application["ServerIPAddress"     ])));
			sb.Append("?Server="   + HttpUtility.UrlEncode(Sql.ToString(Application["ServerName"          ])));
			sb.Append("&Splendid=" + HttpUtility.UrlEncode(Sql.ToString(Application["SplendidVersion"     ])));
			sb.Append("&Key="      + HttpUtility.UrlEncode(Sql.ToString(Application["CONFIG.unique_key"   ])));
			try
			{
				DbProviderFactory dbf = DbProviderFactories.GetFactory(Application);
				using ( IDbConnection con = dbf.CreateConnection() )
				{
					con.Open();
					string sSQL;
					// 10/21/2008 Paul.  Exclude employees from user count. 
					sSQL = "select count(*)    " + ControlChars.CrLf
					     + "  from vwUSERS_List" + ControlChars.CrLf;
					using ( IDbCommand cmd = con.CreateCommand() )
					{
						cmd.CommandText = sSQL;
						sb.Append("&Users=" + Sql.ToString(cmd.ExecuteScalar()));
					}
					sSQL = "select count(*)    " + ControlChars.CrLf
					     + "  from vwUSERS     " + ControlChars.CrLf
					     + " where IS_ADMIN = 1" + ControlChars.CrLf;
					using ( IDbCommand cmd = con.CreateCommand() )
					{
						cmd.CommandText = sSQL;
						sb.Append("&Admins=" + Sql.ToString(cmd.ExecuteScalar()));
					}
					sSQL = "select count(*)    " + ControlChars.CrLf
					     + "  from vwUSERS     " + ControlChars.CrLf
					     + " where IS_GROUP = 1" + ControlChars.CrLf;
					using ( IDbCommand cmd = con.CreateCommand() )
					{
						cmd.CommandText = sSQL;
						sb.Append("&Groups=" + Sql.ToString(cmd.ExecuteScalar()));
					}
					/*
					// Remove redundant information. 
					sSQL = "select count(*)    " + ControlChars.CrLf
					     + "  from vwUSERS     " + ControlChars.CrLf
					     + " where (IS_ADMIN is null or IS_ADMIN = 0)" + ControlChars.CrLf
					     + "   and (IS_GROUP is null or IS_GROUP = 0)" + ControlChars.CrLf;
					using ( IDbCommand cmd = con.CreateCommand() )
					{
						cmd.CommandText = sSQL;
						sb.Append("&Registered=" + Sql.ToString(cmd.ExecuteScalar()));
					}
					*/
					// 01/14/2008 Paul.  SQL Server 2000 cannot count unique Guids. 
					sSQL = "select count(distinct cast(USER_ID as char(36)))" + ControlChars.CrLf
					     + "  from TRACKER                      " + ControlChars.CrLf
					     + " where DATE_ENTERED >= @DATE_ENTERED" + ControlChars.CrLf;
					using ( IDbCommand cmd = con.CreateCommand() )
					{
						cmd.CommandText = sSQL;
						Sql.AddParameter(cmd, "@DATE_ENTERED", DateTime.Today.AddMonths(-1));
						sb.Append("&Activity=" + Sql.ToString(cmd.ExecuteScalar()));
					}

					// 01/14/2008 Paul.  Put the OS Version and SQL Version at the end as they may get truncated. 
					// INETLOG only saves the first 255 of the query string. 
					sSQL = "select Version     " + ControlChars.CrLf
					     + "  from vwSqlVersion" + ControlChars.CrLf;
					using ( IDbCommand cmd = con.CreateCommand() )
					{
						cmd.CommandText = sSQL;
						string sDBVersion = Sql.ToString(cmd.ExecuteScalar());
						sDBVersion = sDBVersion.Replace("Microsoft ", "");
						sDBVersion = sDBVersion.Replace("Intel "    , "");
						sb.Append("&DB=" + HttpUtility.UrlEncode(sDBVersion));
					}
				}
			}
			catch //(Exception ex)
			{
			}
			string sOSVersion = System.Environment.OSVersion.ToString();
			sOSVersion = sOSVersion.Replace("Microsoft "  , "");
			sOSVersion = sOSVersion.Replace("Service Pack", "SP");
			sb.Append("&OS="      + HttpUtility.UrlEncode(sOSVersion));
			// 01/19/2008 Paul.  The application path seems useful, but will usually be /SplendidCRM. 
			sb.Append("&AppPath=" + HttpUtility.UrlEncode(Sql.ToString(Application["ApplicationPath"     ])));
			// 01/19/2008 Paul.  The second least is the .NET version because it will almost always be the current shipping version. 
			sb.Append("&System="  + HttpUtility.UrlEncode(System.Environment.Version.ToString()            ));
			// 01/19/2008 Paul.  The least important piece of information is the SugarVersion.  
			sb.Append("&Sugar="   + HttpUtility.UrlEncode(Sql.ToString(Application["CONFIG.sugar_version"])));
			return sb.ToString();
		}

		// 06/27/2009 Paul.  Add quick function to select a single item in a listbox. 
		public static void SelectItem(ListBox lst, string sValue)
		{
			foreach ( ListItem itm in lst.Items )
			{
				if ( String.Compare(itm.Value, sValue, true) == 0 )
					itm.Selected = true;
			}
		}

		// 12/20/2009 Paul.  Use our own encoding so that a space does not get converted to a +. 
		// 12/20/2009 Paul.  UrlPathEncode converts a space to a %20 whereas UrlEncode converts a space to +. 
		// http://jeays.net/asp.htm
		// http://msdn.microsoft.com/en-us/library/system.web.httpserverutility.urlpathencode.aspx
		public static string ContentDispositionEncode(HttpBrowserCapabilities Browser, string sURL)
		{
			// 01/27/2011 Paul.  Don't use GetFileName as the name may contain reserved directory characters, but expect them to be removed in Utils.ContentDispositionEncode. 
			sURL = sURL.Replace('\\', '_');
			sURL = sURL.Replace(':' , '_');
			// 12/20/2009 Paul.  Make sure that the URL is not null. 
			sURL = Sql.ToString(sURL);
			if ( Browser != null )
			{
				if ( Browser.Browser == "IE" )
				{
					sURL = HttpUtility.UrlPathEncode(sURL);
				}
			}
			sURL = "\"" + sURL + "\"";
			return sURL;
		}

		public static string GenerateVCard(DataRow row)
		{
			StringBuilder sbVCard = new StringBuilder();
			sbVCard.AppendLine("BEGIN:VCARD");
			sbVCard.AppendLine("VERSION:3.0");
			Guid     gID                         = Sql.ToGuid    (row["ID"                        ]);
			string   sSALUTATION                 = Sql.ToString  (row["SALUTATION"                ]).Trim();
			string   sNAME                       = Sql.ToString  (row["NAME"                      ]).Trim();
			string   sFIRST_NAME                 = Sql.ToString  (row["FIRST_NAME"                ]).Trim();
			string   sLAST_NAME                  = Sql.ToString  (row["LAST_NAME"                 ]).Trim();
			string   sTITLE                      = Sql.ToString  (row["TITLE"                     ]).Trim();
			string   sPHONE_HOME                 = Sql.ToString  (row["PHONE_HOME"                ]).Trim();
			string   sPHONE_MOBILE               = Sql.ToString  (row["PHONE_MOBILE"              ]).Trim();
			string   sPHONE_WORK                 = Sql.ToString  (row["PHONE_WORK"                ]).Trim();
			string   sPHONE_OTHER                = Sql.ToString  (row["PHONE_OTHER"               ]).Trim();
			string   sPHONE_FAX                  = Sql.ToString  (row["PHONE_FAX"                 ]).Trim();
			string   sEMAIL1                     = Sql.ToString  (row["EMAIL1"                    ]).Trim();
			string   sEMAIL2                     = Sql.ToString  (row["EMAIL2"                    ]).Trim();
			string   sASSISTANT                  = Sql.ToString  (row["ASSISTANT"                 ]).Trim();
			string   sASSISTANT_PHONE            = Sql.ToString  (row["ASSISTANT_PHONE"           ]).Trim();
			string   sPRIMARY_ADDRESS_STREET     = Sql.ToString  (row["PRIMARY_ADDRESS_STREET"    ]).Trim();
			string   sPRIMARY_ADDRESS_CITY       = Sql.ToString  (row["PRIMARY_ADDRESS_CITY"      ]).Trim();
			string   sPRIMARY_ADDRESS_STATE      = Sql.ToString  (row["PRIMARY_ADDRESS_STATE"     ]).Trim();
			string   sPRIMARY_ADDRESS_POSTALCODE = Sql.ToString  (row["PRIMARY_ADDRESS_POSTALCODE"]).Trim();
			string   sPRIMARY_ADDRESS_COUNTRY    = Sql.ToString  (row["PRIMARY_ADDRESS_COUNTRY"   ]).Trim();
			string   sALT_ADDRESS_STREET         = Sql.ToString  (row["ALT_ADDRESS_STREET"        ]).Trim();
			string   sALT_ADDRESS_CITY           = Sql.ToString  (row["ALT_ADDRESS_CITY"          ]).Trim();
			string   sALT_ADDRESS_STATE          = Sql.ToString  (row["ALT_ADDRESS_STATE"         ]).Trim();
			string   sALT_ADDRESS_POSTALCODE     = Sql.ToString  (row["ALT_ADDRESS_POSTALCODE"    ]).Trim();
			string   sALT_ADDRESS_COUNTRY        = Sql.ToString  (row["ALT_ADDRESS_COUNTRY"       ]).Trim();
			string   sACCOUNT_NAME               = Sql.ToString  (row["ACCOUNT_NAME"              ]).Trim();
			DateTime dtBIRTHDATE                 = DateTime.MinValue;
			// 01/01/2011 Paul.  The Leads module will not have a BIRTHDATE field. 
			if ( row.Table.Columns.Contains("BIRTHDATE") )
				dtBIRTHDATE = Sql.ToDateTime(row["BIRTHDATE"                 ]);
			DateTime dtDATE_MODIFIED_UTC         = Sql.ToDateTime(row["DATE_MODIFIED_UTC"         ]);

			sPRIMARY_ADDRESS_STREET = sPRIMARY_ADDRESS_STREET.Replace("\r\n", "\n");
			sPRIMARY_ADDRESS_STREET = sPRIMARY_ADDRESS_STREET.Replace("\r"  , "\n");
			sALT_ADDRESS_STREET     = sALT_ADDRESS_STREET    .Replace("\r\n", "\n");
			sALT_ADDRESS_STREET     = sALT_ADDRESS_STREET    .Replace("\r"  , "\n");
			string sADDRESS1 = String.Empty;
			string sADDRESS2 = String.Empty;
			if ( !Sql.IsEmptyString(sPRIMARY_ADDRESS_STREET) )
			{
				string[] arrPRIMARY_ADDRESS_STREET = sPRIMARY_ADDRESS_STREET.Split('\n');
				string sPRIMARY_ADDRESS_STREET1 = String.Empty;
				string sPRIMARY_ADDRESS_STREET2 = String.Empty;
				string sPRIMARY_ADDRESS_STREET3 = String.Empty;
				if ( arrPRIMARY_ADDRESS_STREET.Length == 1 )
				{
					sPRIMARY_ADDRESS_STREET3 = arrPRIMARY_ADDRESS_STREET[0];
				}
				else if ( arrPRIMARY_ADDRESS_STREET.Length == 2 )
				{
					sPRIMARY_ADDRESS_STREET2 = arrPRIMARY_ADDRESS_STREET[0];
					sPRIMARY_ADDRESS_STREET3 = arrPRIMARY_ADDRESS_STREET[1];
				}
				else if ( arrPRIMARY_ADDRESS_STREET.Length >= 3 )
				{
					sPRIMARY_ADDRESS_STREET1 = arrPRIMARY_ADDRESS_STREET[0];
					sPRIMARY_ADDRESS_STREET2 = arrPRIMARY_ADDRESS_STREET[1];
					sPRIMARY_ADDRESS_STREET3 = arrPRIMARY_ADDRESS_STREET[2];
				}
				sADDRESS1  =       sPRIMARY_ADDRESS_STREET1   ;  // 1. Post Office Address
				sADDRESS1 += ";" + sPRIMARY_ADDRESS_STREET2   ;  // 2. Extended Address
				sADDRESS1 += ";" + sPRIMARY_ADDRESS_STREET3   ;  // 3. Street
				sADDRESS1 += ";" + sPRIMARY_ADDRESS_CITY      ;  // 4. Locality
				sADDRESS1 += ";" + sPRIMARY_ADDRESS_STATE     ;  // 5. Region
				sADDRESS1 += ";" + sPRIMARY_ADDRESS_POSTALCODE;  // 6. Postal Code
				sADDRESS1 += ";" + sPRIMARY_ADDRESS_COUNTRY   ;  // 7. Country
				//sADDRESS1 = sADDRESS1.Replace("\n", "=0D=0A\n");
			}
			if ( !Sql.IsEmptyString(sALT_ADDRESS_STREET) )
			{
				string[] arrALT_ADDRESS_STREET = sALT_ADDRESS_STREET.Split('\n');
				string sALT_ADDRESS_STREET1 = String.Empty;
				string sALT_ADDRESS_STREET2 = String.Empty;
				string sALT_ADDRESS_STREET3 = String.Empty;
				if ( arrALT_ADDRESS_STREET.Length == 1 )
				{
					sALT_ADDRESS_STREET3 = arrALT_ADDRESS_STREET[0];
				}
				else if ( arrALT_ADDRESS_STREET.Length == 2 )
				{
					sALT_ADDRESS_STREET2 = arrALT_ADDRESS_STREET[0];
					sALT_ADDRESS_STREET3 = arrALT_ADDRESS_STREET[1];
				}
				else if ( arrALT_ADDRESS_STREET.Length >= 3 )
				{
					sALT_ADDRESS_STREET1 = arrALT_ADDRESS_STREET[0];
					sALT_ADDRESS_STREET2 = arrALT_ADDRESS_STREET[1];
					sALT_ADDRESS_STREET3 = arrALT_ADDRESS_STREET[2];
				}
				sADDRESS2  =       sALT_ADDRESS_STREET1       ;  // 1. Post Office Address
				sADDRESS2 += ";" + sALT_ADDRESS_STREET2       ;  // 2. Extended Address
				sADDRESS2 += ";" + sALT_ADDRESS_STREET3       ;  // 3. Street
				sADDRESS2 += ";" + sALT_ADDRESS_CITY          ;  // 4. Locality
				sADDRESS2 += ";" + sALT_ADDRESS_STATE         ;  // 5. Region
				sADDRESS2 += ";" + sALT_ADDRESS_POSTALCODE    ;  // 6. Postal Code
				sADDRESS2 += ";" + sALT_ADDRESS_COUNTRY       ;  // 7. Country
				//sADDRESS2 = sADDRESS2.Replace("\n", "=0D=0A\n");
			}
			
			// http://en.wikipedia.org/wiki/VCard
			// http://www.imc.org/pdi/vcard-21.doc
			sbVCard.AppendLine("N:"  + sLAST_NAME + ";" + sFIRST_NAME + (Sql.IsEmptyString(sSALUTATION) ? String.Empty : ";" + sSALUTATION));
			sbVCard.AppendLine("FN:" + (sSALUTATION + " " + sNAME).Trim());
			if ( !Sql.IsEmptyString(sACCOUNT_NAME) ) sbVCard.AppendLine("ORG:"                 + sACCOUNT_NAME);
			if ( !Sql.IsEmptyString(sTITLE       ) ) sbVCard.AppendLine("TITLE:"               + sTITLE       );
			if ( !Sql.IsEmptyString(sPHONE_HOME  ) ) sbVCard.AppendLine("TEL;TYPE=HOME,VOICE:" + sPHONE_HOME  );
			if ( !Sql.IsEmptyString(sPHONE_MOBILE) ) sbVCard.AppendLine("TEL;TYPE=CELL,VOICE:" + sPHONE_MOBILE);
			if ( !Sql.IsEmptyString(sPHONE_WORK  ) ) sbVCard.AppendLine("TEL;TYPE=WORK,VOICE:" + sPHONE_WORK  );
			if ( !Sql.IsEmptyString(sPHONE_FAX   ) ) sbVCard.AppendLine("TEL;TYPE=WORK,FAX:"   + sPHONE_FAX   );
			if ( !Sql.IsEmptyString(sEMAIL1      ) ) sbVCard.AppendLine("EMAIL;TYPE=INTERNET:" + sEMAIL1      );
			if ( !Sql.IsEmptyString(sASSISTANT   ) ) sbVCard.AppendLine("X-ASSISTANT:"         + sASSISTANT   );
			if ( !Sql.IsEmptyString(sADDRESS1    ) ) sbVCard.AppendLine("ADR;TYPE=WORK"        + (sADDRESS1.IndexOf("=0A=0D") >= 0 ? ";ENCODING=QUOTED-PRINTABLE" : String.Empty) + ":" + sADDRESS1);
			if ( !Sql.IsEmptyString(sADDRESS2    ) ) sbVCard.AppendLine("ADR;TYPE=OTHER"       + (sADDRESS2.IndexOf("=0A=0D") >= 0 ? ";ENCODING=QUOTED-PRINTABLE" : String.Empty) + ":" + sADDRESS2);
			if ( dtBIRTHDATE != DateTime.MinValue  ) sbVCard.AppendLine("BDAY:"                + dtBIRTHDATE.ToString("yyyy-MM-dd"));
			sbVCard.AppendLine("UID:" + gID.ToString());
			sbVCard.AppendLine("REF:" + dtDATE_MODIFIED_UTC.ToString("yyyyMMddTHHmmssZ"));
			sbVCard.AppendLine("END:VCARD");
			return sbVCard.ToString();
		}
	}
}


